﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using MySql.Data.MySqlClient;
using Ymatou.CommonService;
using Ymatou.Evaluate.Common;
using Ymatou.Evaluate.Entity;

namespace Ymatou.Evaluate.Data.Query
{
    public static class EvaluateQueryData
    {
        /// <summary>
        /// 根据买家Id、订单Id集合获取订单状态列表
        /// </summary>
        /// <param name="buyerId"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public static List<EvaluateOrderStatus> GetOrderStatusByBuyerIdOrderIds(int buyerId, List<int> orderIds)
        {
            if (buyerId <= 0 || orderIds == null || !orderIds.Any())
            {
                return null;
            }
            var param = new DynamicParameters(new {buyerId});
            var sql =
                @"SELECT OrderId,SellerId,BuyerId,Status,AddTime,UpdateTime,NOW() SysTime FROM evaluateorderstatus 
                   WHERE BuyerId=@buyerId AND OrderId {0}";

            if (orderIds.Count == 1)
            {
                sql = string.Format(sql, " = @orderId");
                param.AddDynamicParams(new {orderId = orderIds.FirstOrDefault()});
            }
            else
            {
                sql = string.Format(sql, " IN @orderIds");
                param.AddDynamicParams(new {orderIds});
            }

            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<EvaluateOrderStatus>(sql, param).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetOrderStatusByBuyerIdOrderIds发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 获取买家待评价订单Id列表
        /// </summary>
        /// <param name="buyerId"></param>
        /// <param name="lastOrderId"></param>
        /// <param name="pageSize"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static List<int> GetOrderIdsByBuyerId(int buyerId, int? lastOrderId, int pageSize = 10,
            EvaluateStatus status = EvaluateStatus.Pending)
        {
            var sql = string.Format(@"SELECT OrderId 
                                        FROM evaluateorderstatus 
                                       WHERE BuyerId=@buyerId 
                                         AND Status=@status 
                                         AND AddTime > DATE_SUB(NOW(),INTERVAL 60 DAY) {0}
                                    ORDER BY OrderId DESC
                                       LIMIT {1}",
                lastOrderId.HasValue && lastOrderId.Value > 0 ? " AND OrderId < @lastOrderId" : string.Empty, pageSize);
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<int>(sql, new {buyerId, status, lastOrderId}).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetOrderIdsByBuyerId发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 获取买家订单评价状态总数
        /// </summary>
        /// <param name="buyerId"></param>
        /// <param name="lastOrderId"></param>
        /// <param name="pageSize"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static int GetOrderCountByBuyerIdStatus(int buyerId, EvaluateStatus status = EvaluateStatus.Pending)
        {
            const string sql = @"SELECT COUNT(0) TotalNum 
                                   FROM evaluateorderstatus 
                                  WHERE BuyerId=@buyerId 
                                    AND Status=@status 
                                    AND AddTime > DATE_SUB(NOW(),INTERVAL 60 DAY)";
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<int>(sql, new { buyerId, status }).FirstOrDefault();
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetOrderCountByBuyerIdStatus发生SQL异常：{0}", ex.Message), ex);
            }
            return 0;
        }

        /// <summary>
        /// 获取商品评价列表
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="imgs"></param>
        /// <param name="replies"></param>
        /// <returns></returns>
        public static List<EvaluateProduct> GetEvaluateProductList(EvalProdQueryCondition condition,
            out List<EvaluateImgs> imgs, out List<EvaluateReply> replies)
        {
            imgs = null;
            replies = null;

            var param = new DynamicParameters();
            var whereSql = GetEvaluateProductWhereSql(condition, ref param);

            var paginationWhereSql = string.Empty;
            var limitSql = string.Format("{0},{1}", (condition.PageIndex - 1)*condition.PageSize, condition.PageSize);
            
            if (condition.EvalTime.HasValue && condition.ProdEvalId.HasValue)
            {
                limitSql = string.Format("{0},{1}", 0, condition.PageSize);
                paginationWhereSql = GetPaginationWhereSql(condition, ref param);
            }

            var sql = string.Format(
                @"SELECT ProdEvalId,OrderId,SellerId,BuyerId,ProductId,CatalogId,CatalogDesc,Score,IsSensitive
                        ,OrigComments,Comments,ProdEvalTime,EvaluateTp,UpdateTime,IsDeleted,HasImgs,HasReply
                    FROM evaluateproduct {0} {1} 
                ORDER BY HasImgs DESC,HasComment DESC,ProdEvalTime DESC LIMIT {2}", whereSql, paginationWhereSql,
                limitSql);
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<EvaluateProduct>(sql, param).ToList();
                    if (list.Any())
                    {
                        //获取商品评价图片
                        var prodEvalIds = list.Where(l => l.HasImgs).Select(l => l.ProdEvalId).Distinct().ToList();
                        if (prodEvalIds.Any())
                        {
                            sql =
                                "SELECT EvalImgId,ProdEvalId,ImgUrl,AddTime,UpdateTime FROM evaluateimgs WHERE ProdEvalId IN @prodEvalIds";
                            imgs = conn.Query<EvaluateImgs>(sql, new {prodEvalIds}).ToList();
                        }
                        //获取回复 
                        prodEvalIds = list.Where(l => l.HasReply).Select(l => l.ProdEvalId).Distinct().ToList();
                        if (prodEvalIds.Any())
                        {
                            sql =
                                "SELECT EvalReplyId,ProdEvalId,IsSensitive,OrigReply,Reply,ReplyTime FROM evaluatereply WHERE ProdEvalId IN @prodEvalIds";
                            replies = conn.Query<EvaluateReply>(sql, new {prodEvalIds}).ToList();
                    }
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetEvaluateProductList发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 根据查询条件组装where语句
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static string GetEvaluateProductWhereSql(EvalProdQueryCondition condition, ref DynamicParameters param)
        {
            var whereSql = " WHERE ProductId=@productId AND IsDeleted=0 AND EvaluateTp=0";
            param.AddDynamicParams(new {productId = condition.ProductId});
            switch (condition.Level)
            {
                case EvaluateLevel.Top:
                    whereSql += " AND (Score < 0 OR Score > 3)";
                    break;
                case EvaluateLevel.Mid:
                    whereSql += " AND Score = 3";
                    break;
                case EvaluateLevel.Low:
                    whereSql += " AND Score >= 0 AND Score < 3";
                    break;
                case EvaluateLevel.Img:
                    whereSql += " AND HasImgs = 1";
                    break;
                default:
                    whereSql += string.Empty;
                    break;
            }
            return whereSql;
        }

        /// <summary>
        /// 获取分页条件where语句
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static string GetPaginationWhereSql(EvalProdQueryCondition condition, ref DynamicParameters param)
        {
            var whereSql = string.Empty;
            if (condition.EvalTime.HasValue && condition.ProdEvalId.HasValue)
            {
                whereSql += " AND ProdEvalId != @prodEvalId AND ProdEvalTime <= @evalTime";
                param.Add("@prodEvalId", condition.ProdEvalId.Value);
                param.Add("@evalTime", condition.EvalTime.Value);
            }
            return whereSql;
        }

        /// <summary>
        /// 获取商品平均分
        /// </summary>
        /// <param name="productIds"></param>
        /// <returns></returns>
        public static Dictionary<string, decimal> GetProductAvgScoreByIds(List<string> productIds)
        {

            if (productIds == null || !productIds.Any()) return null;

            var sql =
                @"SELECT ProductId Item1,SUM(SCORE) / COUNT(0) Item2 
                    FROM evaluateproduct WHERE ProductId {0} AND IsDeleted=0 AND Score>=0 GROUP BY ProductId ";

            var param = new DynamicParameters();
            if (productIds.Count == 1)
            {
                sql = string.Format(sql, " = @productId");
                param.AddDynamicParams(new {productId = productIds.FirstOrDefault()});
            }
            else
            {
                sql = string.Format(sql, " IN @productIds");
                param.AddDynamicParams(new {productIds});
            }

            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<Tuple<string, decimal>>(sql, param).ToList();
                    if (list.Any())
                    {
                        var dic = new Dictionary<string, decimal>();
                        list.ForEach(p => dic.Add(p.Item1, p.Item2));
                        return dic;
                    }
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetProductAvgScoreByIds发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 获取商品评价状态
        /// </summary>
        /// <param name="sellerId"></param>
        /// <param name="buyerId"></param>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public static List<EvaluateProdStatus> GetProdStatusBySellerIdBuyerIdOrderId(int sellerId, int buyerId,
            int orderId)
        {
            const string sql = @"SELECT OrderId,SellerId,BuyerId,ProductId,CatalogId,Status,AddTime,UpdateTime
                          FROM evaluateprodstatus WHERE OrderId=@orderId AND SellerId=@sellerId AND BuyerId=@buyerId ";
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<EvaluateProdStatus>(sql, new {sellerId, buyerId, orderId}).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetProdStatusBySellerIdBuyerIdOrderId发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 获取过期未评价订单
        /// </summary>
        /// <returns></returns>
        public static List<EvaluateOrderStatus> GetExpiredEvalOrders()
        {
            const string sql =
                @"SELECT OrderId,SellerId,BuyerId,Status,AddTime,UpdateTime 
                    FROM evaluateorderstatus 
                   WHERE Status=0 
                     AND AddTime <= DATE_SUB(NOW(),INTERVAL 60 DAY) ";
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list = conn.Query<EvaluateOrderStatus>(sql).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetExpiredEvalOrders发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 获取评论数量
        /// </summary>
        /// <param name="sellerid"></param>
        /// <param name="score"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static EvaluateCountEntity GetEvaluateCount(int? sellerid, decimal? score, DateTime? beginTime,
            DateTime? endTime)
        {
            var  sql = @"select a.SellerId AS SellerId
                                 ,IFNULL(a.TotalCount,0) as TotalCount,
                                IFNULL(b.ScoreCount,0) as ScoreCount
                                 FROM 
                                (SELECT COUNT(1) AS TotalCount
                                ,SellerId 
                                FROM EvaluateProduct 
                                WHERE 1=1 AND IsDeleted =0 and Score>0 {0}
                                GROUP BY(SellerId)
                                ) a inner  JOIN
                                (SELECT COUNT(1) AS ScoreCount 
                                ,SellerId 
                                FROM EvaluateProduct 
                                WHERE 1=1 AND IsDeleted =0 {1}
                                GROUP BY(SellerId)) b
                                on a.SellerId=b.SellerId
                                ";
         
            var conditionScore = new StringBuilder();
            var queryParameters = new DynamicParameters();
            if (sellerid.HasValue)
            {
                
                conditionScore.Append(" AND SellerId=@SellerId");
                queryParameters.Add("@SellerId", sellerid, DbType.Int32);
            }
           
            if (beginTime.HasValue)
            {
               
                conditionScore.Append(" AND ProdEvalTime >= @beginTime");
                queryParameters.Add("@beginTime", beginTime, DbType.DateTime);
            }
            if (endTime.HasValue)
            {

                conditionScore.Append(" AND ProdEvalTime <= @endTime");
                queryParameters.Add("@endTime", endTime, DbType.DateTime);
            }

            if (score.HasValue)
            {
                queryParameters.Add("@Score", score,DbType.Decimal);
                sql = string.Format(sql, conditionScore, conditionScore + " AND Score BETWEEN 0 AND @Score");
            }
            else
            {
                sql = string.Format(sql, conditionScore , conditionScore);
            }

            
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {

                    var entity = conn.Query<EvaluateCountEntity>(sql, queryParameters).FirstOrDefault();
                    return entity;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetEvaluateCount发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        public static List<EvaluateProduct> GetEvaluateProductList(BaseQueryCondition condition,out int totalCount, out List<EvaluateImgs> imgs,out List<EvaluateReply> replayentitys)
        {
            totalCount = 0;
            imgs = new List<EvaluateImgs>();
            replayentitys = new List<EvaluateReply>();

            var param = new DynamicParameters(condition);
            var whereSql = GetEvaluateProductWhereSql(condition);
            var limitSql = string.Format("{0},{1}", (condition.PageIndex - 1) * condition.PageSize, condition.PageSize);

            var sql = string.Format(
                @"SELECT COUNT(0) FROM evaluateproduct {0};
                  SELECT ProdEvalId,OrderId,SellerId,BuyerId,ProductId,CatalogId,CatalogDesc,Score,IsSensitive
                        ,OrigComments,Comments,ProdEvalTime,EvaluateTp,UpdateTime,IsDeleted,HasImgs,HasReply
                    FROM evaluateproduct {0}  ORDER BY ProdEvalTime DESC LIMIT {1}", whereSql,
                limitSql);
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    using (var multi = conn.QueryMultiple(sql, param))
                    {
                        totalCount = multi.Read<int>().FirstOrDefault();
                        var list = multi.Read<EvaluateProduct>().ToList();
                        if (list.Any())
                        {
                            //获取商品评价图片
                            var prodEvalIds = list.Where(l => l.HasImgs).Select(l => l.ProdEvalId).Distinct().ToList();
                            if (prodEvalIds.Any())
                            {
                                sql = "SELECT EvalImgId,ProdEvalId,ImgUrl,AddTime,UpdateTime FROM evaluateimgs WHERE ProdEvalId IN @prodEvalIds";
                                imgs = conn.Query<EvaluateImgs>(sql, new { prodEvalIds }).ToList();
                            }
                            //获取回复 
                            prodEvalIds = list.Where(l => l.HasReply).Select(l => l.ProdEvalId).Distinct().ToList();
                            if (prodEvalIds.Any())
                            {
                                sql = "SELECT EvalReplyId,ProdEvalId,IsSensitive,OrigReply,Reply,ReplyTime FROM evaluatereply WHERE ProdEvalId IN @prodEvalIds";
                                replayentitys = conn.Query<EvaluateReply>(sql, new { prodEvalIds }).ToList();
                            }
                        }
                        return list;
                    }
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetEvaluateProductList发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 根据查询条件组装where语句
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static string GetEvaluateProductWhereSql(BaseQueryCondition condition)
        {
            var whereSql = " WHERE 1 = 1 AND EvaluateTp=0 AND Score >= 0 ";
            if (condition.ProdEvalId.HasValue && condition.ProdEvalId.Value > 0)
            {
                whereSql += " AND ProdEvalId=@ProdEvalId";
            }
            if (condition.SellerId.HasValue && condition.SellerId.Value > 0)
            {
                whereSql += " AND SellerId=@SellerId";
            }
            if (condition.BuyerId.HasValue && condition.BuyerId.Value > 0)
            {
                whereSql += " AND BuyerId=@BuyerId";
            }
            if (condition.OrderId.HasValue && condition.OrderId.Value > 0)
            {
                whereSql += " AND OrderId=@OrderId";
            }
            if (!string.IsNullOrWhiteSpace(condition.ProductId))
            {
                whereSql += " AND ProductId=@ProductId";
            }
            if (condition.BeginTime.HasValue)
            {
                whereSql += " AND ProdEvalTime >= @BeginTime";
            }
            if (condition.EndTime.HasValue)
            {
                whereSql += " AND ProdEvalTime <= @EndTime";
            }
            if (condition.ScoreList != null && condition.ScoreList.Any())
            {
                whereSql += " AND Score IN @ScoreList ";
            }
            if (condition.IsDeleted.HasValue)
            {
                whereSql += " AND IsDeleted = @IsDeleted ";
            }
            return whereSql;
        }

        public static List<EvaluateOrder> GetEvaluateOrderByOrderIds(List<int> orderIds)
        {
            if (orderIds == null || !orderIds.Any())
            {
                return null;
            }
            orderIds = orderIds.Distinct().ToList();
            var sql =
                string.Format(
                    @"SELECT OrderId,SellerId,BuyerId,EvaluateWay Way,ServiceScore,LogisticsScore,AddTime,UpdateTime,DataType 
                          FROM evaluateorder WHERE OrderId {0}", orderIds.Count == 1 ? " = @OrderId ": " IN @OrderIds ");
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var list =
                        conn.Query<EvaluateOrder>(sql, new {OrderId = orderIds.FirstOrDefault(), OrderIds = orderIds})
                            .ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetEvaluateOrderByOrderIds发生SQL异常：{0}", ex.Message), ex);
            }

            return null;
        }

        /// <summary>
        /// 获取订单评价
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="sellerId"></param>
        /// <param name="buyerId"></param>
        /// <param name="evaluateProducts"></param>
        /// <param name="evaluateImgs"></param>
        /// <returns></returns>
        public static EvaluateOrder GetEvaluateOrder(int orderId, int sellerId, int buyerId,
            out List<EvaluateProduct> evaluateProducts, out List<EvaluateImgs> evaluateImgs)
        {
            evaluateProducts = null;
            evaluateImgs = null;
            var sql =
                @"SELECT OrderId,SellerId,BuyerId,EvaluateWay,ServiceScore,LogisticsScore,AddTime,UpdateTime,DataType
                          FROM evaluateorder WHERE OrderId=@orderId AND SellerId=@sellerId AND BuyerId=@buyerId";
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBReadConnectionString))
                {
                    var evaluateOrder =
                        conn.Query<EvaluateOrder>(sql, new {orderId, sellerId, buyerId}).FirstOrDefault();
                    if (evaluateOrder != null)
                    {
                        sql =
                            @"SELECT ProdEvalId,OrderId,SellerId,BuyerId,ProductId,CatalogId,CatalogDesc,Score,IsSensitive
                                      ,OrigComments,Comments,ProdEvalTime,EvaluateTp,UpdateTime,IsDeleted,HasImgs,DataType
                                  FROM evaluateproduct WHERE OrderId=@orderId AND SellerId=@sellerId AND BuyerId=@buyerId ";
                        evaluateProducts = conn.Query<EvaluateProduct>(sql, new {orderId, sellerId, buyerId}).ToList();
                        if (evaluateProducts.Any())
                        {
                            var prodEvalIds =
                                evaluateProducts.Where(l => l.HasImgs).Select(l => l.ProdEvalId).Distinct().ToList();
                            if (prodEvalIds.Any())
                            {
                                sql =
                                    "SELECT EvalImgId,ProdEvalId,ImgUrl,AddTime,UpdateTime FROM evaluateimgs WHERE ProdEvalId IN @prodEvalIds";
                                evaluateImgs = conn.Query<EvaluateImgs>(sql, new {prodEvalIds}).ToList();
                            }
                        }
                    }
                    return evaluateOrder;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("GetEvaluateOrder发生SQL异常：{0}", ex.Message), ex);
            }
            return null;
        }

        /// <summary>
        /// 判断买手对应商品评价是否存在
        /// </summary>
        /// <param name="sellerId"></param>
        /// <param name="prodEvalId"></param>
        /// <returns></returns>
        public static bool SellerProductEvalExists(int sellerId, int prodEvalId)
        {
            try
            {
                using (var conn = new MySqlConnection(ConnectionStringHelper.EvaluateDBWriteConnectionString))
                {
                    return conn.Query<int>(
                        "select count(0) from evaluateproduct where ProdEvalId=@prodEvalId and SellerId=@sellerId and IsDeleted=0",
                        new { sellerId, prodEvalId })
                        .FirstOrDefault() > 0;
                }
            }
            catch (Exception ex)
            {
                ApplicationLog.Error(string.Format("SellerProductEvalExists发生SQL异常：{0}", ex.Message), ex);
            }
            return false;
        }
    }
}
